package com.learn.english.utils;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.util.FileCopyUtils;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;

/**
 * 数据库初始化工具类
 * 用于通过Java代码执行SQL初始化脚本
 */
@SpringBootTest
public class DatabaseInitializer {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 执行数据库初始化脚本
     */
    @Test
    public void initializeDatabase() {
        try {
            // 读取SQL脚本文件
            Resource resource = new ClassPathResource("init_database.sql");
            Reader reader = new InputStreamReader(resource.getInputStream(), "UTF-8");
            String sql = FileCopyUtils.copyToString(reader);
            
            // 分割SQL语句（以分号为分隔符）
            String[] statements = sql.split(";\s*");
            
            // 执行每条SQL语句
            for (String statement : statements) {
                statement = statement.trim();
                if (!statement.isEmpty()) {
                    try {
                        jdbcTemplate.execute(statement);
                        System.out.println("执行SQL: " + statement.substring(0, Math.min(100, statement.length())) + "...");
                    } catch (Exception e) {
                        System.out.println("SQL执行出错: " + e.getMessage());
                        // 继续执行下一条SQL，忽略已存在的表错误
                    }
                }
            }
            
            System.out.println("数据库初始化完成！");
        } catch (IOException e) {
            System.err.println("读取SQL脚本文件失败: " + e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            System.err.println("数据库初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}